import pandas as pd
import openpyxl
import win32com.client as win32
import os

current_path = os.getcwd()

if __name__ == '__main__':                                  #要要求他们把输入的数据改成xlsx
    original_path = (current_path + "\\发票.xlsx")
    df = pd.read_excel(original_path,engine='openpyxl',header = 0)    #转换格式后输出的文件，读入进行数据处理   
    df_dropped = df.copy()
    df_dropped['工费'] = df_dropped['工费'].astype(float)  
    df_dropped['起版费'] = df_dropped['起版费'].astype(float)  
    df_dropped['金价'] = df_dropped['金价'].astype(float) 

    df_dropped['证书'].fillna(0, inplace=True)
    df_dropped['配件费'].fillna(0, inplace=True)
    df_dropped['工费'].fillna(0, inplace=True)
    df_dropped['起版费'].fillna(0, inplace=True)
    df_dropped['镶工费'].fillna(0, inplace=True)
    df_dropped['露珠或分色费'].fillna(0, inplace=True)
    df_dropped['红宝价格'].fillna(0, inplace=True)
    df_dropped['金额'].fillna(0, inplace=True)
    df_dropped['主镶'].fillna(0, inplace=True)
    df_dropped['金额（主石）'].fillna(0, inplace=True)
    df_dropped['金价'].fillna(0, inplace=True)
    df_dropped['含耗重'].fillna(0, inplace=True)

    #print(df_dropped.dtypes)
    
    df_dropped.loc[:, '应付款'] =   df_dropped['工费'] + df_dropped['镶工费'] + df_dropped['证书'] + df_dropped['配件费']  + df_dropped['起版费'] + df_dropped['镶工费'] + df_dropped['露珠或分色费'] + df_dropped['红宝价格'] + df_dropped['金额'] +df_dropped['主镶'] +df_dropped['金额（主石）'] +df_dropped['金价']*df_dropped['含耗重']
    output = df_dropped.reset_index(drop=True)
    output.to_excel('发票.xlsx', index=False)
   